"""
导出数据类
根据给定的配置文件路径执行sql脚本,导出excel或者csv文件
可根据配置信息串行处理或者并行处理
"""

import csv
import sys
import glob
import threading

import openpyxl
import chardet
from export.DB import DB
from export.SendEmail import *
from export.Config import Config
from export.orders import *
import logging
logger = logging.getLogger(__name__)

# 配置日志文件
BASE_DIR, fn = os.path.split(__file__)


class Export:
    """
    config_path: 提取任务的跟路径: task下的任务名称文件夹
    """
    def __init__(self, config_path):
        self.config_path = config_path
        self.config_file = os.path.join(config_path,  'config.ini')

        # 调用解析类进行配置文件解析
        # print(self.config_file)
        self.config = Config(self.config_file).parse()
        # 获取参数测试
        self.debug = self.config.get('debug')
        self.threads = []

    # 读取sql脚本,过滤注释,分好等字符
    def _readfile(self, fn):
        try:
            # print("开始解析SQL脚本")
            f = open(fn, 'rb')
            data = f.read()
            result = chardet.detect(data)
            data = data.decode(result['encoding'])

            # 过滤注释
            out_sql = []
            da = data.split('\n')
            # print(da)
            for d in da:
                pos = d.find('--')
                if pos != -1:
                    # print(pos)
                    if pos == 0:
                        d = ''
                    else:
                        d = d[:pos]
                out_sql.append(d)
                # print(d)

            if len(out_sql) > 1:
                out_sql = ' '.join(out_sql)
            else:
                out_sql = str(out_sql[0])

            # print("过滤注释以后的sql：" + out_sql)

            # 过滤末尾；号,空行
            out_sql = str(out_sql).replace(';', '').replace('\n', ' ').replace('；', '')
            # print("过滤末尾；以后的sql：" + out_sql)

            # 如果需要换表,则在这里定义换表规则
            if self.config.get('dynamic_table') == 1:
                out_sql = orders_1(out_sql)
            elif self.config.get('dynamic_table') == 2:
                out_sql = orders_2(out_sql)
            elif self.config.get('dynamic_table') == 3:
                out_sql = orders_3(out_sql)
            elif self.config.get('dynamic_table') == 4:
                out_sql = orders_4(out_sql)
            logging.info("数据解析完成!")
            return out_sql

        except Exception as er:
            logger.error(er)
            raise Exception(er)

    # 导出成Excel
    def _export_to_excel(self, sql, server, output_path):
        results, fields = DB().get_data(server, sql)
        # logging.info(results,fields)

        if not results:
            logger.error("导出excel输出错误：{}".format(output_path))
            raise Exception('导出excel输出错误')

        wb = openpyxl.Workbook()
        ws = wb.active

        # 写上字段信息
        fields = [f[0] for f in fields]
        # print(fields)
        ws.append(fields)

        # 写入数据
        logger.info("开始导出到excel")
        for res in results:
            try:
                ws.append(res)
            except openpyxl.utils.exceptions.IllegalCharacterError:
                pass

        wb.save(output_path + '.xlsx')
        logger.info("导出excel成功!")
        return True

    # 导出成csv
    def _export_to_csv(self, sql, server, output_path):
        if not DB().get_data(server, sql):
            return False
        else:
            results, fields = DB().get_data(server, sql)

        logger.info("开始导出到csv")
        try:
            f = open(output_path + '.csv', 'w+', newline='', encoding='utf-8')
            write = csv.writer(f)

            try:
                head = [x[0] for x in fields]
                write.writerow(head)
                write.writerows(results)
            except Exception as e:
                logger.error("写入csv文件失败! 原因：{}".format(e))
            f.close()
        except Exception as e:
            logger.error("写入csv文件出错！原因：{}".format(e))

        logger.info("导出csv成功!")
        return True

    def export(self):
        """
        # db_type
        # 1:paas paas库
        # 2：posp 收单
        # 3：his 收单历史库
        # 4：hlw 互联网库
        # 5：risk 风控库
        # 6：jyx 金盈信库
        # 7：wkkj 无卡快捷
        # 8：bi  bi库
        # 9：paas_new  paas账务
        # 10：business  paas账务
        """

        dbs = ['test', 'paas', 'posp', 'his', 'hlw', 'risk', 'jyx', 'wkkj', 'bi', 'paas_new', 'business', 'fxq']

        for db in dbs:
            posp_sql_files = glob.glob(self.config_path + '\\sql\\' + db + '\\*.*')
            if posp_sql_files:
                # posp_sql_files = [f.replace('\\', '/') for f in posp_sql_files]
                # 并行处理
                if self.config.get('parallel'):
                    threads = []
                    logger.info("导出{}库".format(db))

                    logger.info('批量进行导出')
                    for fp in posp_sql_files:
                        logger.info("开始解析sql脚本:{}".format(fp))

                        # 解析sql
                        sql = self._readfile(fp)

                        # 保存执行结果
                        exports_path = os.path.join(self.config_path, 'exports/')
                        output_file = exports_path + os.path.splitext(os.path.basename(fp))[0]

                        if self.config.get('export_type') == 'excel':
                            postfix = 'xls'
                            task = threading.Thread(target=self._export_to_excel, args=(sql, db, output_file))
                            task.start()
                        elif self.config.get('export_type') == 'csv':
                            postfix = 'csv'
                            task = threading.Thread(target=self._export_to_csv, args=(sql, db, output_file))
                            task.start()

                        self.threads.append(task)

                    for k in threads:
                        k.join()
                else:
                    # 串行处理
                    for fp in posp_sql_files:
                        logger.info("开始解析sql脚本:{}".format(fp))

                        # 解析sql
                        sql = self._readfile(fp)

                        # 保存执行结果
                        exports_path = self.config_path + 'exports/'
                        print(exports_path)
                        output_file = exports_path + os.path.splitext(os.path.basename(fp))[0]

                        if self.config.get('export_type') == 'excel':
                            postfix = 'xls'
                            result = self._export_to_excel(sql, db, output_file)
                        elif self.config.get('export_type') == 'csv':
                            postfix = 'csv'
                            result = self._export_to_csv(sql, db, output_file)


if __name__ == '__main__':
    cfg_path = 'C:\\Users\\xdzf-tf\\PycharmProjects\\mail-issue\\export\\task\\BI系统\\20210305\\运营\\分产品统计交易'
    Export(cfg_path).export()

